﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Data.SqlClient;
using HenIT.Data.SqlClient;
using EventScavenger;
using System.Data;
using System.Text;

namespace EventScavenger
{
	public partial class EventScavengerDAL : GenericSQLServerDAL
	{
		#region Test connection
		public int TestConnection()
		{
			string sql = "GetEventlogRowCount";
			return (int)GetSingleValue(sql);
		} 
		#endregion

		#region MachineLogEntry handling
		public void UpdateMachineLogEntry(MachineLogEntry mle)
		{
			string sql = "UpdateMachineLogEntry";
			try
			{
				object pollfilterIdobj = null;
				if (mle.PollFilterID > 0)
					pollfilterIdobj = mle.PollFilterID;
				SqlParameter[] parameters = new SqlParameter[] 
				{
					new SqlParameter("@CollectorID", mle.CollectorId),
					new SqlParameter("@Machine", mle.Machine),
					new SqlParameter("@LogName", mle.LogName),
					new SqlParameter("@LastPollTime", mle.LastPollTime),
					new SqlParameter("@Enabled", mle.Enabled),
					new SqlParameter("@PollFrequency", mle.PollFrequency),
					new SqlParameter("@MaxEntryCount", mle.MaxEntryCount),
					new SqlParameter("@PollFilterID", pollfilterIdobj)
				};
				ExecuteSP(sql, parameters);
			}
			catch { }
		}
		public List<MachineLogEntry> GetMachineLogListAll()
		{
			List<MachineLogEntry> list = new List<MachineLogEntry>();
			string sql = "SelectMachineLogListAll";
			DataSet mleds = GetDataSet(sql, CommandType.StoredProcedure);
			if (mleds.Tables.Count > 0 && mleds.Tables[0].Rows.Count > 0)
			{
				foreach (DataRow dr in mleds.Tables[0].Rows)
				{
					MachineLogEntry mle = new MachineLogEntry(dr["Machine"].ToString(), dr["LogName"].ToString());
					mle.MachineLogID = (int)dr["MachineLogID"];
					mle.CollectorId = (int)dr["CollectorID"];
					mle.Collector = dr["Collector"].ToString();
					mle.Enabled = (bool)dr["Enabled"];
					mle.PollFrequency = (int)dr["PollFrequency"];
					mle.MaxEntryCount = (int)dr["MaxEntryCount"];
					if (dr["LastPollTime"] != DBNull.Value)
						mle.LastPollTime = (DateTime)dr["LastPollTime"];
					if (dr["PollFilterID"] != DBNull.Value)
						mle.PollFilterID = (int)dr["PollFilterID"];
					list.Add(mle);
				}
			}
			return list;
		}
		public List<MachineLogEntry> GetMachineLogList(bool hideDisdabled)
		{
			List<MachineLogEntry> list = new List<MachineLogEntry>();
			string sql = "SelectMachineLogList2";

			SqlParameter[] parameters = new SqlParameter[] 
				{
					new SqlParameter("@hideDisabled", hideDisdabled),
				};
			DataSet mleds = GetDataSet(sql, CommandType.StoredProcedure, parameters);
			if (mleds.Tables.Count > 0 && mleds.Tables[0].Rows.Count > 0)
			{
				foreach (DataRow dr in mleds.Tables[0].Rows)
				{
					MachineLogEntry mle = new MachineLogEntry(dr["Machine"].ToString(), dr["LogName"].ToString());
					mle.MachineLogID = (int)dr["MachineLogID"];
					mle.CollectorId = (int)dr["CollectorID"];
					mle.Collector = dr["Collector"].ToString();
					mle.Enabled = (bool)dr["Enabled"];
					mle.PollFrequency = (int)dr["PollFrequency"];
					mle.MaxEntryCount = (int)dr["MaxEntryCount"];
					if (dr["LastPollTime"] != DBNull.Value)
						mle.LastPollTime = (DateTime)dr["LastPollTime"];
					if (dr["Oldest Entry"] != DBNull.Value)
					{
						DateTime t;
						if (DateTime.TryParse(dr["Oldest Entry"].ToString(), out t))
							mle.OldestEntry = t;
					}
					if (dr["Newest Entry"] != DBNull.Value)
					{
						DateTime t;
						if (DateTime.TryParse(dr["Newest Entry"].ToString(), out t))
							mle.NewestEntry = t;
					}
					int tint = 0;
					if (int.TryParse(dr["Entry Count"].ToString(), out tint))
						mle.EntryCount = tint;
					if (dr["PollFilterID"] != DBNull.Value)
						mle.PollFilterID = (int)dr["PollFilterID"];

					list.Add(mle);
				}
			}            

			return list;
		}
		internal void DeleteMachineLog(int machineLogId)
		{
			string sql = "DeleteMachineLog";
			SqlParameter[] parameters = new SqlParameter[] 
				{
					new SqlParameter("@MachineLogID", machineLogId)
				};
			ExecuteSP(sql, parameters);
		}
		#endregion

		#region Settings
		private T ReadSettingValue<T>(object dvalue, T defaultValue)
		{
			T returnValue = defaultValue;
			if (dvalue != DBNull.Value)
				returnValue = (T)dvalue;
			return returnValue;
		}
		private DateTime ReadSettingValueDate(object dvalue, DateTime defaultValue)
		{
			DateTime returnValue = defaultValue;
			if (dvalue != DBNull.Value)
				returnValue = DateTime.Parse(dvalue.ToString());
			return returnValue;
		}
		public GlobalSettingsData GetGlobalSettings()
		{
			GlobalSettingsData globalSettingsData = new GlobalSettingsData();
			string sql = "SelectGlobalSettings";
			DataSet ds = base.GetDataSet(sql, CommandType.StoredProcedure);
			if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
			{
				DataRow r = ds.Tables[0].Rows[0];
				globalSettingsData.MaxLogEntries = ReadSettingValue<int>(r["MaxLogEntries"], globalSettingsData.MaxLogEntries);
				globalSettingsData.MaintenanceEnabled = ReadSettingValue<bool>(r["MaintenanceEnabled"], globalSettingsData.MaintenanceEnabled);
				globalSettingsData.MaintenanceFrequencyMinutes = ReadSettingValue<int>(r["MaintenanceFrequencyMinutes"], globalSettingsData.MaintenanceFrequencyMinutes);
				globalSettingsData.MaintenanceRunner = ReadSettingValue<string>(r["MaintenanceRunner"].ToString(), globalSettingsData.MaintenanceRunner);
				globalSettingsData.MaintenanceLastRun = ReadSettingValueDate(r["MaintenanceLastRun"], globalSettingsData.MaintenanceLastRun);
				globalSettingsData.MaintenanceTimeOutSeconds = ReadSettingValue(r["MaintenanceTimeOutSeconds"], globalSettingsData.MaintenanceTimeOutSeconds);
				globalSettingsData.RecyclingCheckFrequencyMinutes = ReadSettingValue<int>(r["RecyclingCheckFrequencyMinutes"], globalSettingsData.RecyclingCheckFrequencyMinutes);
				globalSettingsData.NextCreateHistoryTime = ReadSettingValueDate(r["NextCreateHistoryTime"], globalSettingsData.NextCreateHistoryTime);
				globalSettingsData.RunCreateHistoryEnabled = ReadSettingValue<bool>(r["RunCreateHistoryEnabled"], globalSettingsData.RunCreateHistoryEnabled);
				globalSettingsData.NextCreateHistoryFrequencyMinutes = ReadSettingValue<int>(r["NextCreateHistoryFrequencyMinutes"], globalSettingsData.NextCreateHistoryFrequencyMinutes);
				globalSettingsData.DaysToHistory = ReadSettingValue<int>(r["DaysToHistory"], globalSettingsData.DaysToHistory);
				globalSettingsData.RepeatTimeSec = ReadSettingValue<int>(r["RepeatTimeSec"], globalSettingsData.RepeatTimeSec);
			}
			return globalSettingsData;
		}
		public void SetGlobalSettings(GlobalSettingsData globalsettings)
		{
			string sql = "UpdateGlobalSettings";
			SqlParameter[] parameters = new SqlParameter[] 
				{
					new SqlParameter("@MaxLogEntries", globalsettings.MaxLogEntries),
					new SqlParameter("@MaintenanceEnabled", globalsettings.MaintenanceEnabled),
					new SqlParameter("@MaintenanceFrequencyMinutes", globalsettings.MaintenanceFrequencyMinutes),
					new SqlParameter("@MaintenanceRunner", globalsettings.MaintenanceRunner),
					new SqlParameter("@RecyclingCheckFrequencyMinutes" , globalsettings.RecyclingCheckFrequencyMinutes),
					new SqlParameter("@NextCreateHistoryTime", globalsettings.NextCreateHistoryTime.ToString("yyyy-MM-dd HH:mm")),
					new SqlParameter("@RunCreateHistoryEnabled", globalsettings.RunCreateHistoryEnabled),
					new SqlParameter("@NextCreateHistoryFrequencyMinutes", globalsettings.NextCreateHistoryFrequencyMinutes),
					new SqlParameter("@DaysToHistory", globalsettings.DaysToHistory),
					new SqlParameter("@RepeatTimeSec", globalsettings.RepeatTimeSec),
					new SqlParameter("@MaintenanceTimeOutSeconds", globalsettings.MaintenanceTimeOutSeconds)
				};
			ExecuteSP(sql, parameters);
		}
		#endregion

		#region Collectors
		public List<Collector> GetCollectorList()
		{
			List<Collector> collectors = new List<Collector>();
			string sql = "SelectCollectors";
			DataSet colds = GetDataSet(sql, CommandType.StoredProcedure);
			if (colds.Tables.Count > 0 && colds.Tables[0].Rows.Count > 0)
			{
				collectors.AddRange(
						(from DataRow r in colds.Tables[0].Rows
						 select new Collector()
						 {
							 Id = (int)r["CollectorID"],
							 Name = r["Name"].ToString(),
							 ThreadRecycleFrequencyMinutes = (int)r["ThreadRecycleFrequencyMinutes"],
							 NextRecycleTime = r["NextRecycleTime"] == System.DBNull.Value ? new DateTime(2000,1,1) : (DateTime)r["NextRecycleTime"],
							 ThreadRecyclingEnabled = (bool)r["ThreadRecyclingEnabled"],
							 ImportsEnabled = (bool)r["ImportsEnabled"],
							 ImportFrequency = (int)r["ImportFrequencyMinutes"]
						 }
						)
						);
			}
			return collectors;
		}
		public void UpdateCollector(Collector collector)
		{
			string sql = "UpdateCollector";
			SqlParameter[] parameters = new SqlParameter[] 
				{
					new SqlParameter("@CollectorID", collector.Id),
					new SqlParameter("@Name", collector.Name),
					new SqlParameter("@ThreadRecycleFrequencyMinutes", collector.ThreadRecycleFrequencyMinutes),
					new SqlParameter("@NextRecycleTime", collector.NextRecycleTime),
					new SqlParameter("@ThreadRecyclingEnabled", collector.ThreadRecyclingEnabled),
					new SqlParameter("@ImportsEnabled", collector.ImportsEnabled),
					new SqlParameter("@ImportFrequencyMinutes", collector.ImportFrequency)
				};
			ExecuteSP(sql, parameters);
		}
		public void InsertCollector(Collector collector)
		{
			string sql = "InsertCollector";
			SqlParameter[] parameters = new SqlParameter[] 
				{
					new SqlParameter("@Name", collector.Name),
					new SqlParameter("@ThreadRecycleFrequencyMinutes", collector.ThreadRecycleFrequencyMinutes),
					new SqlParameter("@NextRecycleTime", collector.NextRecycleTime),
					new SqlParameter("@ThreadRecyclingEnabled", collector.ThreadRecyclingEnabled),
					new SqlParameter("@ImportsEnabled", collector.ImportsEnabled),
					new SqlParameter("@ImportFrequencyMinutes", collector.ImportFrequency)
				};
			ExecuteSP(sql, parameters);
		}
		public void DeleteCollector(Collector collector)
		{
			string sql = "DeleteCollector";
			SqlParameter[] parameters = new SqlParameter[] 
				{
					new SqlParameter("@CollectorID", collector.Id)
				};
			ExecuteSP(sql, parameters);
		} 
		#endregion

		#region ImportLogs
		public List<ImportForeignLog> GetImportLogs()
		{
			string sql = "SelectImportLogs";
			List<ImportForeignLog> list = new List<ImportForeignLog>();
			try
			{
				DataSet importList = base.GetDataSet(sql, CommandType.StoredProcedure);
				if (importList.Tables.Count > 0)
				{
					list.AddRange(from DataRow row in importList.Tables[0].Rows
								  select new ImportForeignLog()
								  {
									  ForeignLogId = (int)row["ForeignLogID"],
									  CollectorId = (int)row["CollectorID"],
									  CollectorName = row["CollectorName"].ToString(),
									  SQLServer = row["Server"].ToString(),
									  Database = row["Database"].ToString(),
									  MachineLogId = (int)row["MachineLogID"],
									  Machine = row["Machine"].ToString(),
									  LogName = row["LogName"].ToString(),
									  Enabled = (bool)row["Enabled"]
								  }
								 );
				}
			}
			catch (Exception ex)
			{
				lastError = ex.ToString();
			}

			return list;
		}
		public void UpdateImportForeignLog(ImportForeignLog importLog)
		{
			string sql = "UpdateImportLogs";
			SqlParameter[] parameters = new SqlParameter[] 
				{
					new SqlParameter("@ForeignLogID", importLog.ForeignLogId),
					new SqlParameter("@CollectorID", importLog.CollectorId),
					new SqlParameter("@Server", importLog.SQLServer),
					new SqlParameter("@Database", importLog.Database),
					new SqlParameter("@MachineLogID", importLog.MachineLogId),
					new SqlParameter("@Enabled", importLog.Enabled)
				};
			ExecuteSP(sql, parameters);
		}
		public void InsertImportForeignLog(ImportForeignLog importLog)
		{
			string sql = "InsertImportLogs";
			SqlParameter[] parameters = new SqlParameter[] 
				{
					new SqlParameter("@CollectorID", importLog.CollectorId),
					new SqlParameter("@Server", importLog.SQLServer),
					new SqlParameter("@Database", importLog.Database),
					new SqlParameter("@MachineLogID", importLog.MachineLogId),
					new SqlParameter("@Enabled", importLog.Enabled)
				};
			ExecuteSP(sql, parameters);
		}
		public void DeleteImportForeignLog(ImportForeignLog importLog)
		{
			string sql = "DeleteImportLogs";
			SqlParameter[] parameters = new SqlParameter[] 
				{
					new SqlParameter("@ForeignLogID", importLog.ForeignLogId)
				};
			ExecuteSP(sql, parameters);
		}
		#endregion

		#region Polling filters
		public List<PollFilter> GetPollingFilterList()
		{
			List<PollFilter> filters = new List<PollFilter>();
			string sql = "SelectPollingFilters";
			DataSet pfds = GetDataSet(sql, CommandType.StoredProcedure);
			if (pfds.Tables.Count > 0 && pfds.Tables[0].Rows.Count > 0)
			{
				foreach (DataRow r in pfds.Tables[0].Rows)
				{
					PollFilter pollFilter = new PollFilter()
					{
						PollFilterID = (int)r["PollFilterID"],
						Name = r["Name"].ToString(),
						MessageContains = (r["MessageContains"] == DBNull.Value ? "" : r["MessageContains"].ToString()),
						ExcludeInfo = (bool)r["ExcludeInfo"],
						ExcludeWarn = (bool)r["ExcludeWarn"],
						ExcludeErr = (bool)r["ExcludeErr"],
						ExcludeSucAud = (bool)r["ExcludeSucAud"],
						ExcludeFailAud = (bool)r["ExcludeFailAud"],
						LastChanged = (r["LastChanged"] == DBNull.Value ? null : (DateTime?)r["LastChanged"])
					};
					if ((int)r["EventIDFilterCount"] > 0)
					{
						sql = "SelectPollFilterEventIDsByPollFilterID";
						SqlParameter[] parameters2 = new SqlParameter[] 
						{
							new SqlParameter("@PollFilterID", pollFilter.PollFilterID),
						};
						DataSet pfeidds = GetDataSet(sql, CommandType.StoredProcedure, parameters2);
						if (pfeidds.Tables.Count > 0 && pfeidds.Tables[0].Rows.Count > 0)
						{
							foreach (DataRow rid in pfeidds.Tables[0].Rows)
							{
								if ((bool)rid["Exclude"])
								{
									pollFilter.ExcludeEventIDs.Add((int)rid["EventID"]);
								}
								else
								{
									pollFilter.IncludeEventIDs.Add((int)rid["EventID"]);
								}
							}
						}
					}
					if ((int)r["SourceFilterCount"] > 0)
					{
						sql = "SelectPollFilterSourcesByPollFilterID";
						SqlParameter[] parameters3 = new SqlParameter[] 
						{
							new SqlParameter("@PollFilterID", pollFilter.PollFilterID),
						};
						DataSet pfsds = GetDataSet(sql, CommandType.StoredProcedure, parameters3);
						if (pfsds.Tables.Count > 0 && pfsds.Tables[0].Rows.Count > 0)
						{
							foreach (DataRow rs in pfsds.Tables[0].Rows)
							{
								if ((bool)rs["Exclude"])
								{
									pollFilter.ExcludeSources.Add(rs["Source"].ToString());
									pollFilter.ExcludeSourceIDs.Add((int)rs["SourceID"]);
								}
								else
								{
									pollFilter.IncludeSources.Add(rs["Source"].ToString());
									pollFilter.IncludeSourceIDs.Add((int)rs["SourceID"]);
								}
							}
						}
					}
					filters.Add(pollFilter);
				}
 
			}
			return filters;
		}
		public void UpdatePollFilter(PollFilter pollFilter)
		{
			string sql = "UpdatePollFilter";
			StringBuilder pollFilterEventIDIncludes = new StringBuilder();
			StringBuilder pollFilterEventIDExcludes = new StringBuilder();
			StringBuilder pollFilterSourceIncludes = new StringBuilder();
			StringBuilder pollFilterSourceExcludes = new StringBuilder();
			if (pollFilter.IncludeEventIDs.Count() > 0)
			{
				foreach (int i in pollFilter.IncludeEventIDs)
				{
					pollFilterEventIDIncludes.Append(i.ToString() + ",");
				}
			}
			if (pollFilter.ExcludeEventIDs.Count() > 0)
			{
				foreach (int i in pollFilter.ExcludeEventIDs)
				{
					pollFilterEventIDExcludes.Append(i.ToString() + ",");
				}
			}
			if (pollFilter.IncludeSourceIDs.Count() > 0)
			{
				foreach (int i in pollFilter.IncludeSourceIDs)
				{
					pollFilterSourceIncludes.Append(i.ToString() + ",");
				}
			}
			if (pollFilter.ExcludeSourceIDs.Count() > 0)
			{
				foreach (int i in pollFilter.ExcludeSourceIDs)
				{
					pollFilterSourceExcludes.Append(i.ToString() + ",");
				}
			}

			SqlParameter[] parameters = new SqlParameter[] 
				{
					new SqlParameter("@PollFilterID", pollFilter.PollFilterID),
					new SqlParameter("@Name", pollFilter.Name),
					new SqlParameter("@MessageContains", pollFilter.MessageContains),
					new SqlParameter("@ExcludeInfo", pollFilter.ExcludeInfo),
					new SqlParameter("@ExcludeWarn", pollFilter.ExcludeWarn),
					new SqlParameter("@ExcludeErr", pollFilter.ExcludeErr),
					new SqlParameter("@ExcludeSucAud", pollFilter.ExcludeSucAud),
					new SqlParameter("@ExcludeFailAud", pollFilter.ExcludeFailAud),
					new SqlParameter("@PollFilterEventIDIncludes", pollFilterEventIDIncludes.ToString().TrimEnd(',')),
					new SqlParameter("@PollFilterEventIDExcludes", pollFilterEventIDExcludes.ToString().TrimEnd(',')),
					new SqlParameter("@PollFilterSourceIncludes", pollFilterSourceIncludes.ToString().TrimEnd(',')),
					new SqlParameter("@PollFilterSourceExcludes", pollFilterSourceExcludes.ToString().TrimEnd(','))
				};
			ExecuteSP(sql, parameters);
		}
		public void InsertPollFilter(PollFilter pollFilter)
		{
			string sql = "InsertPollFilter";
			StringBuilder pollFilterEventIDIncludes = new StringBuilder();
			StringBuilder pollFilterEventIDExcludes = new StringBuilder();
			StringBuilder pollFilterSourceIncludes = new StringBuilder();
			StringBuilder pollFilterSourceExcludes = new StringBuilder();
			if (pollFilter.IncludeEventIDs.Count() > 0)
			{
				foreach (int i in pollFilter.IncludeEventIDs)
				{
					pollFilterEventIDIncludes.Append(i.ToString() + ",");
				}
			}
			if (pollFilter.ExcludeEventIDs.Count() > 0)
			{
				foreach (int i in pollFilter.ExcludeEventIDs)
				{
					pollFilterEventIDExcludes.Append(i.ToString() + ",");
				}
			}
			if (pollFilter.IncludeSourceIDs.Count() > 0)
			{
				foreach (int i in pollFilter.IncludeSourceIDs)
				{
					pollFilterSourceIncludes.Append(i.ToString() + ",");
				}
			}
			if (pollFilter.ExcludeSourceIDs.Count() > 0)
			{
				foreach (int i in pollFilter.ExcludeSourceIDs)
				{
					pollFilterSourceExcludes.Append(i.ToString() + ",");
				}
			}

			SqlParameter[] parameters = new SqlParameter[] 
				{
					new SqlParameter("@Name", pollFilter.Name),
					new SqlParameter("@MessageContains", pollFilter.MessageContains),
					new SqlParameter("@ExcludeInfo", pollFilter.ExcludeInfo),
					new SqlParameter("@ExcludeWarn", pollFilter.ExcludeWarn),
					new SqlParameter("@ExcludeErr", pollFilter.ExcludeErr),
					new SqlParameter("@ExcludeSucAud", pollFilter.ExcludeSucAud),
					new SqlParameter("@ExcludeFailAud", pollFilter.ExcludeFailAud),
					new SqlParameter("@PollFilterEventIDIncludes", pollFilterEventIDIncludes.ToString().TrimEnd(',')),
					new SqlParameter("@PollFilterEventIDExcludes", pollFilterEventIDExcludes.ToString().TrimEnd(',')),
					new SqlParameter("@PollFilterSourceIncludes", pollFilterSourceIncludes.ToString().TrimEnd(',')),
					new SqlParameter("@PollFilterSourceExcludes", pollFilterSourceExcludes.ToString().TrimEnd(','))
				};
			ExecuteSP(sql, parameters);
		}
		public void DeletePollFilter(PollFilter pollFilter)
		{
			string sql = "DeletePollFilter";
			SqlParameter[] parameters = new SqlParameter[] 
				{
					new SqlParameter("@PollFilterID", pollFilter.PollFilterID)
				};
			ExecuteSP(sql, parameters);
		}
		#endregion

		#region Event sources
		public List<EventSource> GetEventSourceList()
		{
			List<EventSource> list = new List<EventSource>();
			string sql = "SelectSourceList2";
			DataSet dsSources = base.GetDataSet(sql, CommandType.StoredProcedure);
			if (dsSources.Tables.Count > 0)
			{
				list.AddRange(
					from DataRow r in dsSources.Tables[0].Rows
					select new EventSource() { SourceID = (int)r["SourceID"], Source = r["Source"].ToString() }
					);
			}

			return list;
		}  
		#endregion
	}
}
